import {
  SqlBuildResult,
  TABLE_DATA_TYPE,
  TableBuildResult,
  TableColumn,
  TableDataType,
  TableDefinition,
  TableIndex,
  Entity
} from "./types";
import {SqlExecuteResult, SqlExecutor} from "../execution";
import {Database} from "better-sqlite3";

/**
 * 表构建器
 * 用于构建建表SQL语句
 * @template T 表对应的实体类型
 */
export class TableBuilder<T = any> {
  /** 表名 */
  private tableName: string;
  /** 列定义数组 */
  private columns: TableColumn<T>[] = [];
  /** 已添加的列名集合，用于检查字段是否已存在 */
  private columnNames: Set<string> = new Set<string>();
  /** 索引定义数组 */
  private indices: TableIndex<T>[] = [];
  /** 是否是临时表 */
  private isTemporary: boolean = false;
  /** 是否不存在则创建 */
  private ifNotExists: boolean = false;
  /** SQL执行器 */
  private executor?: SqlExecutor;

  /**
   * 构造函数
   * @param tableName 表名
   */
  constructor(tableName: string) {
    this.tableName = tableName;
  }

  /**
   * 设置db数据库，相当于通过数据库添加执行器
   * @param db 数据库
   * @returns 当前构建器实例
   */
  public setDB(db: Database): TableBuilder<T> {
    this.executor = new SqlExecutor(db);
    return this;
  }

  /**
   * 设置SQL执行器
   * @param executor SQL执行器
   * @returns 当前表构建器实例
   */
  public setExecutor(executor: SqlExecutor): TableBuilder<T> {
    this.executor = executor;
    return this;
  }

  /**
   * 获取SQL执行器
   * @returns SQL执行器实例
   */
  public getExecutor(): SqlExecutor | undefined {
    return this.executor;
  }

  /**
   * 设置为临时表
   * @returns 当前表构建器实例
   */
  public temporary(): TableBuilder<T> {
    this.isTemporary = true;
    return this;
  }

  /**
   * 设置为如果不存在则创建
   * @returns 当前表构建器实例
   */
  public ifNotExist(): TableBuilder<T> {
    this.ifNotExists = true;
    return this;
  }

  /**
   * 添加列定义
   * @param column 列定义
   * @returns 当前表构建器实例
   * @throws 如果列名已存在则抛出错误
   */
  public addColumn<R = any>(column: TableColumn<T, R>): TableBuilder<T> {
    const columnName = String(column.name);
    if (this.columnNames.has(columnName)) {
      throw new Error(`Column ${columnName} already exists in table ${this.tableName}`);
    }

    this.columns.push(column);
    this.columnNames.add(columnName);
    return this;
  }

  /**
   * 添加指定类型的列
   * @param name 列名
   * @param type 列类型
   * @param options 列选项
   * @returns 当前表构建器实例
   */
  public column<R = any>(
    name: Extract<keyof T, string> | string,
    type: TableDataType,
    options: Partial<TableColumn<T, R>> = {}
  ): TableBuilder<T> {
    return this.addColumn({
      name,
      type,
      ...options
    });
  }

  /**
   * 添加整数列
   * @param name 列名
   * @param options 列选项
   * @returns 当前表构建器实例
   */
  public integer<R = any>(name: Extract<keyof T, string> | string, options: Partial<TableColumn<T, R>> = {}): TableBuilder<T> {
    return this.column(name, TABLE_DATA_TYPE.INTEGER, options);
  }

  /**
   * 添加文本列
   * @param name 列名
   * @param options 列选项
   * @returns 当前表构建器实例
   */
  public text<R = any>(name: Extract<keyof T, string> | string, options: Partial<TableColumn<T, R>> = {}): TableBuilder<T> {
    return this.column(name, TABLE_DATA_TYPE.TEXT, options)
  }

  /**
   * 添加实数列
   * @param name 列名
   * @param options 列选项
   * @returns 当前表构建器实例
   */
  public real<R = any>(name: Extract<keyof T, string> | string, options: Partial<TableColumn<T, R>> = {}): TableBuilder<T> {
    return this.column(name, TABLE_DATA_TYPE.REAL, options);
  }

  /**
   * 添加BLOB列
   * @param name 列名
   * @param options 列选项
   * @returns 当前表构建器实例
   */
  public blob<R = any>(name: Extract<keyof T, string> | string, options: Partial<TableColumn<T, R>> = {}): TableBuilder<T> {
    return this.column(name, TABLE_DATA_TYPE.BLOB, options);
  }

  /**
   * 添加自动主键列，自动递增
   * @param name 列名，默认为'id'
   * @param options 列选项
   * @returns 当前表构建器实例
   */
  public autoId<R = any>(name: Extract<keyof T, string> | string = 'id', options: Partial<Omit<TableColumn<T, R>, 'primaryKey'>> = {}): TableBuilder<T> {
    return this.column(name, TABLE_DATA_TYPE.INTEGER, {
      primaryKey: true,
      autoIncrement: true,
      ...options
    })
  }

  /**
   * 添加默认值的时间戳列
   * 在SQLite中，时间戳可以存储为TEXT、REAL或INTEGER
   * 这里使用ISO8601格式的TEXT类型，默认值为CURRENT_TIMESTAMP
   * @param name 列名
   * @param options 列选项
   * @returns 当前表构建器实例
   */
  public defaultTimestamp<R = any>(name: Extract<keyof T, string> | string, options: Partial<TableColumn<T, R>> = {}): TableBuilder<T> {
    return this.addColumn({
      name,
      type: TABLE_DATA_TYPE.TEXT,
      defaultValue: 'CURRENT_TIMESTAMP',
      ...options
    });
  }

  /**
   * 添加索引
   * @param columns 索引列
   * @param options 索引选项
   * @returns 当前表构建器实例
   */
  public index(
    columns: string | string[] | keyof T | Array<keyof T>,
    options: { name?: string, unique?: boolean } = {}
  ): TableBuilder<T> {
    const columnsArray = Array.isArray(columns)
      ? (columns as any[]).map(c => c.toString())
      : [columns.toString()];

    const name = options.name || `${this.tableName}_${columnsArray.join('_')}_idx`;

    this.indices.push({
      name,
      columns: columnsArray,
      unique: options.unique || false
    });

    return this;
  }

  /**
   * 添加唯一索引
   * @param columns 索引列
   * @param options 索引选项
   * @returns 当前表构建器实例
   */
  public unique(
    columns: string | string[] | keyof T | Array<keyof T>,
    options: { name?: string } = {}
  ): TableBuilder<T> {
    return this.index(columns, {...options, unique: true});
  }

  /**
   * 构建SQL语句
   * @returns 表构建结果
   */
  public build(): TableBuildResult {
    const params: Record<string, any> = {};
    let sql = '';

    // 创建表
    sql += this.isTemporary ? 'CREATE TEMPORARY TABLE ' : 'CREATE TABLE ';

    // 如果不存在则创建
    if (this.ifNotExists) {
      sql += 'IF NOT EXISTS ';
    }

    // 表名
    sql += `${this.tableName} (\n`;

    // 列定义
    const columnDefs = this.columns.map(column => {
      const columnName = String(column.name);
      let def = `  ${columnName} ${column.type}`;

      // 主键
      if (column.primaryKey) {
        def += ' PRIMARY KEY';

        // 自增
        if (column.autoIncrement) {
          def += ' AUTOINCREMENT';
        }
      }

      // 非空
      if (column.notNull) {
        def += ' NOT NULL';
      }

      // 唯一
      if (column.unique) {
        def += ' UNIQUE';
      }

      // 默认值
      if (column.defaultValue !== undefined) {
        if (typeof column.defaultValue === 'string') {
          // 判断是否是SQL表达式
          if (this.isSqlExpression(column.defaultValue)) {
            // SQL表达式不加引号
            def += ` DEFAULT ${column.defaultValue}`;
          } else {
            // 普通字符串加引号
            def += ` DEFAULT '${column.defaultValue}'`;
          }
        } else if (column.defaultValue === null) {
          def += ' DEFAULT NULL';
        } else if (typeof column.defaultValue === 'boolean') {
          def += ` DEFAULT ${column.defaultValue ? 1 : 0}`;
        } else if (typeof column.defaultValue === 'number') {
          def += ` DEFAULT ${column.defaultValue}`;
        } else {
          def += ` DEFAULT '${column.defaultValue}'`;
        }
      }

      // 检查约束
      if (column.check) {
        def += ` CHECK (${column.check})`;
      }

      // 外键引用
      if (column.references) {
        def += ` REFERENCES ${column.references.table}(${column.references.column})`;

        // 更新行为
        if (column.references.onUpdate) {
          def += ` ON UPDATE ${column.references.onUpdate}`;
        }

        // 删除行为
        if (column.references.onDelete) {
          def += ` ON DELETE ${column.references.onDelete}`;
        }
      }

      return def;
    });

    sql += columnDefs.join(',\n');
    sql += '\n)';

    // 构建创建索引的SQL语句
    const indexSqls = this.indices.map(index => {
      const uniqueStr = index.unique ? 'UNIQUE ' : '';
      return `CREATE ${uniqueStr}INDEX IF NOT EXISTS ${index.name} ON ${this.tableName} (${index.columns.join(', ')})`;
    });

    return {
      sql,
      params,
      indexSqls
    };
  }

  /**
   * 判断是否为SQL表达式
   * @param value 字符串值
   * @returns 是否为SQL表达式
   */
  private isSqlExpression(value: string): boolean {
    const sqlKeywords = [
      'CURRENT_TIMESTAMP', 'CURRENT_TIME', 'CURRENT_DATE',
      'strftime', 'datetime', 'date', 'time',
      '(', ')', '+', '-', '*', '/',
      'NULL', 'TRUE', 'FALSE'
    ];

    return sqlKeywords.some(keyword => value.includes(keyword));
  }

  /**
   * 执行SQL创建表
   * @param executor 可选的SQL执行器，如果未提供则使用内部设置的执行器
   * @returns 执行结果
   */
  public execute(executor?: SqlExecutor): SqlExecuteResult {
    const result = this.build() as SqlBuildResult & { indexSqls: string[] };
    const actualExecutor = executor || this.executor;

    if (!actualExecutor) {
      throw new Error('No SQL executor available. Please set an executor using setExecutor() or provide one as parameter.');
    }

    // 执行创建表语句
    const tableResult = actualExecutor.execute(result.sql, result.params);

    // 执行创建索引语句
    for (const indexSql of result.indexSqls) {
      actualExecutor.execute(indexSql, {});
    }

    return tableResult;
  }

  /**
   * 创建表构建器实例
   * @param tableName 表名
   * @returns 表构建器实例
   */
  public static create<T = any>(tableName: string): TableBuilder<T> {
    return new TableBuilder<T>(tableName);
  }

  /**
   * 从JSON配置创建表
   * @param definition 表定义
   * @returns 表构建器实例
   */
  public static fromJSON<T = any>(definition: TableDefinition<T>): TableBuilder<T> {
    const builder = new TableBuilder<T>(definition.name);

    // 设置表选项
    if (definition.temporary) {
      builder.temporary();
    }

    if (definition.ifNotExists) {
      builder.ifNotExist();
    }

    // 添加列
    for (const column of definition.columns) {
      builder.addColumn(column);
    }

    // 添加索引
    if (definition.indices) {
      for (const index of definition.indices) {
        builder.index(index.columns as any, {name: index.name, unique: index.unique});
      }
    }

    return builder;
  }
}
